﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Audit_Configuration_Oracle.Model;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace Audit_Configuration_Oracle.DAL
{
    public class HistoryDAL
    {
        private const string QUERY_TEMPLATE = "SELECT {0}  FROM DBA_AUDIT_TRAIL {1}";
        private const string DESC_AUDIT = "SELECT COLUMN_NAME from user_tab_columns where table_name='DBA_AUDIT_TRAIL'";
        private static string QUERY = string.Empty;
        public static string[] DEFAULT_COLUMNS = { "OS_USERNAME", "USERNAME", "TIMESTAMP", "OWNER", "OBJ_NAME", "ACTION_NAME", "OBJ_PRIVILEGE", "AUDIT_OPTION", "SES_ACTIONS", "LOGOFF_TIME", "SQL_TEXT"};

        private static List<string> columnsDisplay = null;

        private static void SetDefaultColumns() 
        {
            columnsDisplay = new List<string>();
            columnsDisplay.Add("OS_USERNAME");
            columnsDisplay.Add("USERNAME");
            columnsDisplay.Add("TIMESTAMP");
            columnsDisplay.Add("OWNER");
            columnsDisplay.Add("OBJ_NAME");
            columnsDisplay.Add("ACTION_NAME");
            columnsDisplay.Add("OBJ_PRIVILEGE");
            columnsDisplay.Add("AUDIT_OPTION");
            columnsDisplay.Add("SES_ACTIONS");
            columnsDisplay.Add("LOGOFF_TIME");
            columnsDisplay.Add("SQL_TEXT");
        }

        public static DataSet GetResutls(string dateStart, string dateEnd, string user, string obj, List<string> customCols = null)
        {
            DataSet ds = null;
            DataTable dt = null;
                      
            DBConnectionSing db = null;

            try
            {
                if (customCols == null || customCols.Count == 0)
                {
                    SetDefaultColumns();
                }
                else
                {
                    columnsDisplay = customCols;
                }             

                CreateTable(out dt);

                db = DBConnectionSing.Db;
                db.openConnection();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;

                    PrepareQuery(dateStart, dateEnd, user, obj);
                    cmd.CommandText = QUERY;

                    using (OracleDataReader dr = cmd.ExecuteReader())
                    {
                        ds = new DataSet();
                        while (dr.Read())
                        {
                            DataRow rw = dt.NewRow();
                            for (int i = 0; i < columnsDisplay.Count; i++)
                            {
                                rw[columnsDisplay[i]] = dr[columnsDisplay[i]].ToString();
                            }
                            /*rw["OS_USERNAME"] = dr["OS_USERNAME"].ToString();
                            rw["USERNAME"] = dr["USERNAME"].ToString();
                            //rw["TIMESTAMP"] = dr["TIMESTAMP"].ToString();
                            rw["OWNER"] = dr["OWNER"].ToString();
                            rw["OBJ_NAME"] = dr["OBJ_NAME"].ToString();
                            rw["ACTION_NAME"] = dr["ACTION_NAME"].ToString();
                            rw["OBJ_PRIVILEGE"] = dr["OBJ_PRIVILEGE"].ToString();
                            rw["AUDIT_OPTION"] = dr["AUDIT_OPTION"].ToString();
                            rw["SES_ACTIONS"] = dr["SES_ACTIONS"].ToString();
                            rw["LOGOFF_TIME"] = dr["LOGOFF_TIME"].ToString();
                            rw["SQL_TEXT"] = dr["LOGOFF_TIME"].ToString();*/
                            dt.Rows.Add(rw);
                        }
                        dr.Close();
                    }
                }
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            ds.Tables.Add(dt);

            return ds;
        }

        private static void CreateTable(out DataTable dt) 
        {
            dt = new DataTable("TableAuditHistory");

            for (int i = 0; i < columnsDisplay.Count; i++)
            {
                dt.Columns.Add(columnsDisplay[i], typeof(string));                
            }
        }

        private static void PrepareQuery(string dateStart, string dateEnd, string user, string obj) 
        {
            StringBuilder colsToRetrieve = new StringBuilder();
            StringBuilder conditions = new StringBuilder();
            string middleCondition = "{0} {1}";

            QUERY = QUERY_TEMPLATE;

            for (int i = 0; i < columnsDisplay.Count; i++)
            {
                if (i != columnsDisplay.Count - 1)
                {
                    colsToRetrieve.Append(string.Format("{0}, ", columnsDisplay[i]));
                }
                else
                {
                    colsToRetrieve.Append(string.Format("{0}", columnsDisplay[i]));
                }
            }

            if(dateStart.Length > 0 && dateEnd.Length > 0)
            {
               //OracleTimeStamp timestamp  =  new OracleTimeStamp(date);                
               //conditions.Append(string.Format("WHERE TIMESTAMP >= '{0}'", timestamp.ToOracleDate()));               
                conditions.Append(string.Format("WHERE TIMESTAMP >= TO_DATE('{0}','DD-MON-YY') AND TIMESTAMP <= TO_DATE('{1}','DD-MON-YY')",
                    string.Format("{0:dd-MMM-yy}", DateTime.Parse(dateStart)), string.Format("{0:dd-MMM-yy}", DateTime.Parse(dateEnd).AddDays(1))));
            }

            if (!user.Equals(""))
            {
                if (conditions.Length > 1)
                {
                    conditions.Append(string.Format(" AND USERNAME = '{0}'", user));
                }
                else
                {
                    conditions.Append(string.Format(" WHERE USERNAME = '{0}'", user));
                }
            }

            if (!obj.Equals(""))
            {
                if (conditions.Length > 1)
                {
                    conditions.Append(string.Format(" AND OBJ_NAME = '{0}'", obj));
                }
                else
                {
                    conditions.Append(string.Format(" WHERE OBJ_NAME = '{0}'", obj));
                }
            }

            QUERY = string.Format(QUERY, colsToRetrieve.ToString(), conditions.ToString());
        }

        public static List<string> GetAuditColumns()
        {
            List<string> res = null;
            DBConnectionSing db = null;

            try
            {   
                db = DBConnectionSing.Db;
                db.openConnection();

                using (OracleCommand cmd = new OracleCommand())
                {
                    cmd.Connection = db.conn;                   
                    cmd.CommandText = DESC_AUDIT;

                    using (OracleDataReader dr = cmd.ExecuteReader())
                    {
                        res = new List<string>();
                        while (dr.Read())
                        {
                            res.Add(dr["COLUMN_NAME"].ToString());
                        }
                        dr.Close();
                    }
                }
            }
            catch (OracleException e)
            {
                throw e;
            }
            finally
            {
                db.closeConnection();
                db = null;
            }
            return res;
        }
    }
}
